/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.x3.purchasing.account;

import com.x3.purchasing.common.ClassConnection;
import com.x3.purchasing.role.RoleDAOImpl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author kris
 */
public class AccountDAOImpl implements AccountDAO {

    public AccountDAOImpl() {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public void insert(Account account) throws Exception {
        String sql = "INSERT INTO account(username,password,role_id) VALUES(?,?,?)";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    account.getUsername(),
                    account.getPassword(),
                    account.getRole().getId()
                });
    }

    public void update(Account account) throws Exception {
        String sql = "UPDATE account SET username = ?, password=? WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    account.getUsername(),
                    account.getPassword(),
                    account.getId()
                });
    }

    public void delete(int id) throws Exception {
        String sql = "DELETE FROM account WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{id});
    }

    public Account get(int id) throws Exception {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Account getMaxId() throws Exception {
        Account account = null;
        String sql = "SELECT MAX(id) as id FROM account";
        account = (Account) ClassConnection.getJdbc().queryForObject(sql, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Account temp = new Account();
                temp.setId(rs.getInt(1));
//                temp.setUsername(rs.getString(2));
//                temp.setPassword(rs.getString(3));
                return temp;
            }
        });
        return account;
    }

    public List<Account> gets() throws Exception {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Account getByUsername(String username) throws Exception {
        String sql = "SELECT * FROM account WHERE username = '" + username + "'";
        return (Account) ClassConnection.getJdbc().queryForObject(sql, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Account account = new Account();
                account.setId(rs.getInt("id"));
                account.setUsername(rs.getString("username"));
                return account;
            }
        });
    }

    public Account login(String userName, String password) throws Exception {
        Account acc = null;
        final String psw = password;
        String sql = "SELECT id,username,password,role_id FROM account "
                + "WHERE username=?";
        acc = (Account) ClassConnection.getJdbc().queryForObject(sql, new Object[]{userName}, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Account tmp = null;
                if (rs.getString(3).equals(psw)) {
                    tmp = new Account();
                    tmp.setId(rs.getInt(1));
                    tmp.setUsername(rs.getString(2));
                    tmp.setPassword(rs.getString(3));                    
                    try {
                        tmp.setRole(new RoleDAOImpl().getRole(rs.getInt(4)));
                    } catch (Exception ex) {
                        Logger.getLogger(AccountDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                return tmp;
            }
        });
        return acc;
    }
}
